package com.oocl.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.dao.PersonDao;
import com.oocl.server.po.Deptment;
import com.oocl.server.po.Person;
import com.oocl.util.DbUtil;

public class PersonDaoImpl implements PersonDao{

	@Override
	public int addPerson(Person p) {
		String sql = "insert into person(pid,pname,birthday,tel,did,salary) values (avril_01.nextval,?,?,?,?,?)";
		
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtil.connect();
		
		int m = 0;//执行成功的条数
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);	
			pst.setString(1, p.getPname());
			pst.setDate(2, p.getBirthday());
			pst.setString(3, p.getTel());
			pst.setInt(4, p.getDid());
			pst.setInt(5, p.getSalary());
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			DbUtil.free(con, pst, null);
		}
		return m;
	}

	@Override
	public int delPerson(int pid) {
		String sql = "delete from person where pid=?";
		
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtil.connect();
		
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setInt(1, pid);
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			DbUtil.free(con, pst, null);
		}
		return m;
	}

	@Override
	public int updatePerson(Person p) {
		String sql = "update person set pname=?,birthday=?,tel=?,did=?,salary=? where pid=?";
		
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtil.connect();
		
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setString(1, p.getPname());
			pst.setDate(2, new Date(p.getBirthday().getTime()));
			pst.setString(3, p.getTel());
			pst.setInt(4, p.getDid());
			pst.setInt(5, p.getSalary());
			pst.setInt(6, p.getPid());
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}finally {
			DbUtil.free(con, pst, null);
		}
		return m;
	}

	@Override
	public List<Person> belongDetp() {
		String sql = "select * from person LEFT JOIN deptment ON person.did = deptment.did";
		
		Person p = new Person();
		Deptment d = new Deptment();
		List<Person> persons = new ArrayList<Person>();
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtil.connect();
		
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			rs= pst.executeQuery();
			while (rs.next()) {
				p = new Person();
				d = new Deptment();
				p.setPid(rs.getInt("pid"));
				p.setPname(rs.getString("pname"));
				p.setBirthday(rs.getDate("birthday"));
				p.setTel(rs.getString("tel"));
				p.setDid(rs.getInt("did"));
				p.setSalary(rs.getInt("salary"));
				d.setDname(rs.getString("dname"));
				d.setCity(rs.getString("city"));
				p.setDept(d);
				persons.add(p);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbUtil.free(con, pst, rs);
		}
		return persons;
	}

	@Override
	public List<Person> showDeptPerson(int did) {
		List<Person> lp = new ArrayList<Person>();
		String sql = "select * from person where did=?";
		
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtil.connect();
		
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, did);
			rs= pst.executeQuery();
			while (rs.next()) {
				Person p = new Person();
				p.setPid(rs.getInt("pid"));
				p.setPname(rs.getString("pname"));
				p.setBirthday(rs.getDate("birthday"));
				p.setTel(rs.getString("tel"));
				p.setDid(rs.getInt("did"));
				p.setSalary(rs.getInt("salary"));
				lp.add(p);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbUtil.free(con, pst, rs);
		}
		
		return lp;
	}

}
